package com.bolt.support.codegenerator.db;

import com.bolt.common.db.DbHelper;
import com.bolt.common.utils.StrUtil;
import com.bolt.convention.data.DefaultPageInfo;
import com.bolt.convention.data.PageInfo;
import com.bolt.convention.exception.ServiceException;
import com.bolt.support.codegenerator.dto.TableInfo;
import com.bolt.support.codegenerator.model.Table;
import com.bolt.support.sqlconfig.dialect.PagingOracleDialect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author michao
 *         Created by Administrator on 2018/2/2.
 */
public class OracleTableFactory extends AbstractTableFactory {
    private static final String TABLE_COMMENTS_SQL = "select * from user_tab_comments where TABLE_NAME=?";
    private static final String COLUMN_COMMENTS_SQL = "select * from user_col_comments where TABLE_NAME=?";


    public OracleTableFactory(String url, String username, String password, String driverClass) {
        super(url, username, password, driverClass);
    }

    @Override
    public Table getTable(String tableName) throws SQLException {
        Table table = super.getTable(tableName);
        if (table != null) {
            introspectTableComments(table, TABLE_COMMENTS_SQL, "COMMENTS");
            introspectTableColumnsComments(table, COLUMN_COMMENTS_SQL, "COLUMN_NAME", "COMMENTS");
        }
        return table;
    }

    @Override
    public PageInfo<TableInfo> getTables(String tableName, int page, int size) throws SQLException {
        String sql = "select dt.table_name table_name," +
                " dtc.comments  table_comment," +
                " uo.created    create_time" +
                " from user_tables dt, user_tab_comments dtc, user_objects uo" +
                " where dt.table_name = dtc.table_name" +
                " and dt.table_name = uo.object_name" +
                " and uo.object_type = 'TABLE' " +
                " and dt.table_name like ? " +
                " order by uo.CREATED desc";

        String totalSql = "select COUNT(*)" +
                "  from user_tables dt, user_tab_comments dtc, user_objects uo" +
                "  where dt.table_name = dtc.table_name" +
                "  and dt.table_name = uo.object_name" +
                "  and uo.object_type = 'TABLE' " +
                "  and dt.table_name like ? " +
                "  order by uo.CREATED desc";


        PagingOracleDialect dialect = new PagingOracleDialect();
        String limitSql = dialect.getLimitString(sql, page * size, size);
        List<TableInfo> datas = new ArrayList<>();
        PreparedStatement limitStmt = null;
        PreparedStatement countStmt = null;
        Connection connection = null;
        ResultSet limitRs = null;
        ResultSet countRs = null;
        long total = 0;
        try {
            connection = getConnection();
            limitStmt = connection.prepareStatement(limitSql);
            limitStmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");
            limitRs = limitStmt.executeQuery();//执行查询操作
            while (limitRs.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(limitRs.getString("table_name"));
                tableInfo.setRemark(limitRs.getString("table_comment"));
                tableInfo.setCreateTime(limitRs.getDate("create_time"));
                datas.add(tableInfo);
            }
            countStmt = connection.prepareStatement(totalSql);
            countStmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");
            countRs = countStmt.executeQuery();
            if (countRs.next()) {
                total = countRs.getLong(1);
            }
            return new DefaultPageInfo(page, size, datas, total);
        } catch (Exception e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(limitRs);
            DbHelper.close(countRs);
            DbHelper.close(limitStmt);
            DbHelper.close(countStmt);
            DbHelper.close(connection);
        }
    }
}
